IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdDetail_Update'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdDetail_Update
GO

CREATE PROC dbo.pInProdDetail_Update
	@inProdDetailId INT,
	@amount INT = NULL,
	@inputPrice INT = NULL	
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo        INT,
    @strErrMsg       NVARCHAR(400),
    @intReturn       INT,
    @oldAmount		 INT,
	@intBillDetailId	INT,
	@intOldInputPrice	INT,
	@intInProdPaperId	INT,
	@intProductId		INT,
	@datInProdDate		DATETIME
	

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/

	BEGIN TRANSACTION
	BEGIN TRY		
		SELECT @oldAmount = InProdDetail.Amount,
			@intOldInputPrice = InProdDetail.InputPrice,
			@intInProdPaperId = InProdDetail.InProdPaperId,
			@intProductId = ProductId
		FROM InProdDetail
		WHERE Id = @inProdDetailId
	
		-- Update InProdDetail
		UPDATE InProdDetail
		SET Amount = ISNULL(@amount, Amount),
			InputPrice = ISNULL(@inputPrice, InputPrice)
		WHERE Id = @inProdDetailId
		
		IF @oldAmount <> @amount BEGIN
			UPDATE Product
			SET Amount = Amount - @oldAmount + @amount
			WHERE Id = @intProductId			
		END -- end compare old amount vs new amount
		
		IF @intOldInputPrice <> @inputPrice BEGIN
			-- Update latest input price of product
			EXEC pUpdateLatestInputPriceOfProduct
				@vintProductId = @intProductId
		
			SELECT @datInProdDate = Date
			FROM InProdPaper
			WHERE Id = @intInProdPaperId
			
			-- Update input price of bill detail which sale time after input product time
			UPDATE BillDetail
			SET InputPrice = @inputPrice
			FROM BillDetail
			INNER JOIN Bill
				ON BillDetail.BillId = Bill.Id
			WHERE Bill.IsPrinted = 1 
				AND BillDetail.ProductId = @intProductId 
				AND (DATEDIFF(DAY, @datInProdDate, Bill.SaleTime) >= 0)
		END
		
		SET @intReturn = 0
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SET @intReturn = -1
		ROLLBACK TRANSACTION
	END CATCH
		
	SELECT @intReturn 



